﻿using CommHelper;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Security.Policy;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Net.Mime.MediaTypeNames;

namespace NX5WinServies
{
    public class Words
    {
        public class WordSyllable
        {
            public String Method { set; get; }
        }

        public class WordMethodItem
        {
            public String Method { set; get; }

            public String Detail { set; get; }
        }

        public void SDEMethods()
        {
            DataManager dm = new DataManager();
            DataTable dt = dm.SqlCmd("SELECT * FROM Base_Words WHERE LinkSDE = 0").table;
            foreach(DataRow dr in dt.Rows)
            {
                try
                {
                    DataTable wdt = dm.SqlCmd("select distinct MemoryType,MemoryText from SDE.dbo.Word_Version where Word = '" + dr["Word"].ToString().Replace("'", "''").Replace("--", "") + "' and len(MemoryType) > 2 and len(MemoryText) > 2 and IsSure = 1").table;

                    List<WordMethodItem> Mts = Newtonsoft.Json.JsonConvert.DeserializeObject<List<WordMethodItem>>(dr["Methods"].ToString());

                    foreach (DataRow sderow in wdt.Rows)
                    {
                        WordMethodItem item = new WordMethodItem();
                        item.Method = sderow["MemoryType"].ToString();
                        item.Detail = sderow["MemoryText"].ToString();
                        Mts.Insert(0, item);
                    }

                    DataTable imgdt = dm.SqlCmd("select top 1 'https://www.abc5g.com'+TagImage TagImage from SDE.dbo.Word_Version where Word = '" + dr["Word"].ToString().Replace("'", "''").Replace("--", "") + "' and len(TagImage) > 10 and IsSure = 1").table;
                    if (imgdt.Rows.Count > 0)
                    {
                        String Url = imgdt.Rows[0]["TagImage"].ToString();
                        String File = Url.Replace("https://www.abc5g.com", "E:\\Webs\\SDE.Web.Deploy").Replace("/", "\\");
                        FileInfo fi = new FileInfo(File);
                        if (fi.Exists)
                        {    
                            WordMethodItem item = new WordMethodItem();
                            item.Method = "图片助记法";
                            item.Detail = imgdt.Rows[0]["TagImage"].ToString();
                            Mts.Insert(wdt.Rows.Count, item);
                        }
                        else
                        {
                            Log.Write("BaseWord-SDEMethod", dr["Word"].ToString() + "======>" + Url + "========>不存在");
                        }
                    }

                    dm.Procedure("Cus_UpdateWordMethods_SDE", "[{\"ID\":" + dr["ID"].ToString()
                            + ",\"Methods\":\"" + TransData.Tran(Newtonsoft.Json.JsonConvert.SerializeObject(Mts)) + "\"}]");

                    Console.WriteLine(dr["Word"].ToString() + "============>" + Newtonsoft.Json.JsonConvert.SerializeObject(Mts));
                }
                catch(Exception ex)
                {
                    Log.Write("BaseWord-SDEMethod", dr["Word"].ToString() + "======>" + ex.ToString());
                }
            }
        }

        public void Syllable()
        {
            DataManager dm = new DataManager();
            dm.SqlCmd("UPDATE Base_Words SET Syllable = K.Syllable,IsAISyllable = 1 "
                  +" FROM Base_Words BW INNER JOIN ( "
                  +" SELECT DISTINCT BW.Word,BW.ID,BW.MP3,WV.Syllable FROM Base_Words BW "
                  +" INNER JOIN SDE.DBO.Word_Version WV ON BW.Word = WV.Word " 
                  +" WHERE BW.IsAISyllable = 0 and charindex('/',WV.Syllable) > 0 AND LEN(WV.SYLLABLE) > 3) K on K.ID = bw.ID");
        }

        public async Task Method()
        {
            DataManager dm = new DataManager();
            DataTable dt = dm.SqlCmd("select * from Base_Words where IsAIMethod = 0 and AIError = 0").table;
            Log.Write("BaseWord-Method", "找到" + dt.Rows.Count + "个需要处理的单词");
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    if (!Boolean.Parse(dr["IsAIMethod"].ToString()))
                    {
                        Log.Write("BaseWord-Method", dr["ID"].ToString() + "===========>" + dr["ID"].ToString());
                        String Result = await SDKs.AIDoubao.AIBase("我需要\"" + dr["Word"].ToString() 
                            + "\"这个单词的记忆方法(优先音节记忆和词根词缀)，最少给5种记忆方法。返回json数组格式:[{\"Method\":\"方法名\",\"Detail\":\"说明\"}]。不要解释", false);
                        Log.Write("BaseWord-Method", Result);

                        //WordSyllable WSL = JsonConvert.DeserializeObject<WordSyllable>(Result);
                        //dm.SqlCmd("update Base_Words set Methods = N'" + Result.Replace("'", "''").Replace("--", "——")
                        //    + "',IsAIMethod = 1 where id = " + dr["ID"].ToString());

                        dm.Procedure("Cus_UpdateWordMethods", "[{\"ID\":" + dr["ID"].ToString()
                            + ",\"Methods\":\"" + TransData.Tran(Result) + "\"}]");


                        //Log.Write("BaseWord-Method", dr["ID"].ToString() + "===================================>" + WSL.Method.Replace("'", "''").Replace("--", "——"));
                    }
                    else
                    {
                        dm.SqlCmd("update Base_Words set IsAIMethod = 1 where id = " + dr["ID"].ToString());
                    }
                }
                catch (Exception ex)
                {
                    dm.SqlCmd("update Base_Words set AIError = 1 where id = " + dr["ID"].ToString());
                    Log.Write("BaseWord-Method", dr["ID"].ToString() + "===================================>" + ex.ToString());
                }
            }
        }


        public async Task MP3()
        {
            DataManager dm = new DataManager();
            DataTable dt = dm.SqlCmd("SELECT * FROM Base_Words WHERE MP3 IS NULL").table;
            Log.Write("BaseWord-MP3", "找到" + dt.Rows.Count + "个需要处理的单词");
            foreach (DataRow dr in dt.Rows)
            {
                try
                { 
                    Log.Write("BaseWord-MP3", dr["ID"].ToString() + "===========>" + dr["ID"].ToString());

                    String Word = dr["Word"].ToString();
                    if(Word.Split(' ').Length > 1 || Word.IndexOf("...") != -1)
                    {
                        Word = Word.Replace("sb", "somebody").Replace("sth", "something").Replace("...", " ");
                    } 

                    String Audio = await SDKs.AIDoubao.AIAudio(Word);

                    if (String.IsNullOrEmpty(Audio))
                    {
                        Log.Write("BaseWord-MP3", dr["ID"].ToString() + "=====>空音频===>" + Audio);
                        continue;
                    }
                    else if (Audio.Length < 40)
                    {
                        Log.Write("BaseWord-MP3", dr["ID"].ToString() + "=====>空音频===>" + Audio);
                        continue;
                    }

                    dm.SqlCmd("update Base_Words set MP3 = N'" + Audio
                        + "' where id = " + dr["ID"].ToString());


                    Log.Write("BaseWord-MP3", dr["ID"].ToString() + "===================================>" + Audio);


                }
                catch (Exception ex)
                { 
                    Log.Write("BaseWord-MP3", dr["ID"].ToString() + "===================================>" + ex.ToString());
                }
            }
        }
    }
}
